# -*- utf-8 -*-
import logging
import datetime
import platform

from sqlalchemy import func, select, text, table, delete, column

from utils.network import Result, NeedLoginView, ExecuteSql, CustomGenericViewSet
from utils.others import sqlalchemy_to_dict, datetime_format
from .sqla_models import Brand, ShopInfo, Platform, get_engine, get_session, Account, get_tables, get_primary_keys
import pandas as pd
from utils import sql_, others
from .utils import field_alias_list

logger = logging.getLogger(__name__)


def platform_count_sql(tb, d1, d2, field='update_time', using_func='COUNT', function_text='*',
                       filter_where='platform_id = -1'):
    sql = """
SELECT ifnull({}({}), 0) AS result 
FROM {}
WHERE {} <= '{}' AND {} >= '{}' AND {}
    """.format(using_func, function_text, tb, field, datetime_format(d1, '%Y-%m-%d'), field,
               datetime_format(d2, '%Y-%m-%d'), filter_where)
    return sql


def get_seven_time():
    last_day = datetime.datetime.strptime((datetime.datetime.now() + datetime.timedelta(days=1)).strftime("%Y-%m-%d"),
                                          "%Y-%m-%d")
    now_zero = datetime.datetime.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), "%Y-%m-%d")
    top_time = now_zero
    seven_day = [(last_day, now_zero)]
    for i in range(1, 7):
        last_day = top_time - datetime.timedelta(days=1)
        seven_day.append((top_time, last_day))
        top_time = last_day
    return reversed(seven_day)


def get_seven_day_sql(tb, platform_id: int, **kwargs):
    seven_day = get_seven_time()
    sqls = [
        platform_count_sql(tb=tb,
                           d1=s_item[0],
                           d2=s_item[1],
                           filter_where=f"platform_id = {platform_id}",
                           **kwargs)
        for s_item in seven_day
    ]

    return sqls


def get_seven_data(tb, platform_dict_list, **kwargs):
    engine = get_engine()
    sqls = [{'name': pdl['name'],
             'sql': get_seven_day_sql(tb=tb, platform_id=pdl['id'], **kwargs)}
            for pdl in platform_dict_list]
    seven_day = get_seven_time()
    data_list = {'xAxis': [datetime_format(i[0], format_='%Y-%m-%d') for i in seven_day], 'yAxis': []}
    for bt_item in sqls:
        item = {'name': bt_item['name'], 'data': []}
        for sql in bt_item['sql']:
            df = pd.read_sql(sql, engine)
            item['data'].append(df['result'][0])
        data_list['yAxis'].append(item)

    return data_list


class TransactionView(NeedLoginView):

    def get(self, request):
        # ------------------------------查询总数-----------------------------------
        session = get_session(get_engine())
        branch_count = session.query(Brand).count()
        shop_count = session.query(ShopInfo).count()
        sum_money = session.query(func.sum(ShopInfo.money * ShopInfo.sales)).all()
        platform_count = session.query(Platform).count()
        # -------------------------------查询最近七天----------------------------------
        platform_list = session.query(Platform.name, Platform.id).all()
        platform_dict_list = sqlalchemy_to_dict(platform_list, ['name', 'id'])
        brand_data = get_seven_data(tb='brand',
                                    platform_dict_list=platform_dict_list)
        shop_data = get_seven_data(tb='shop_info',
                                   platform_dict_list=platform_dict_list)
        shop_money_data = get_seven_data(tb='shop_info',
                                         platform_dict_list=platform_dict_list,
                                         using_func='SUM', function_text='money')
        return Result.success(data={
            'count': [
                {
                    'name': 'shop_count',
                    'value': shop_count,
                    'icon_class': 'shopping',
                    'div_icon_class': 'card-panel-icon-wrapper icon-shopping',
                    'text': '商品数量 / 个'
                }, {
                    'name': 'sum_money',
                    'value': sum_money[0][0],
                    'icon_class': 'money',
                    'div_icon_class': 'card-panel-icon-wrapper icon-money',
                    'text': '总价值 / 元'
                }, {
                    'name': 'branch_count',
                    'value': branch_count,
                    'icon_class': 'peoples',
                    'div_icon_class': 'card-panel-icon-wrapper icon-message',
                    'text': '商家数量 / 位'
                }, {
                    'name': 'platform_count',
                    'value': platform_count,
                    'icon_class': 'component',
                    'div_icon_class': 'card-panel-icon-wrapper icon-message',
                    'text': '平台 / 个'
                },
            ],
            'charts_data': {
                'shop_count': shop_data,
                'sum_money': shop_money_data,
                'branch_count': brand_data,
                'platform_count': None
            }
        })


class SystemInfoView(NeedLoginView):

    def get(self, request):
        info = [
            {'key': '操作系统及版本信息', 'value': platform.platform()},
            {'key': '系统版本号', 'value': platform.version()},
            {'key': '系统名称', 'value': platform.system()},
            {'key': '系统位数', 'value': platform.architecture()},
            {'key': '计算机类型', 'value': platform.machine()},
            {'key': '计算机名称', 'value': platform.node()},
            {'key': '处理器类型', 'value': platform.processor()},
            {'key': '计算机相关信息', 'value': ' '.join(platform.uname())},
        ]
        return Result.success(data=info)


class RankingListView(NeedLoginView, ExecuteSql):
    def post(self, request):
        # 商品分页数据
        rankQuery = request.data['rankQuery'] if 'rankQuery' in request.data.keys() else {}
        rankQuery.setdefault('pageSize', 3)
        rank_page_size = int(rankQuery.get('pageSize'))

        engine = get_engine()
        session = get_session(engine)
        # 排行榜
        lg_shop_sql = session.query(ShopInfo, Brand, Platform) \
            .join(Brand, ShopInfo.brand_id == Brand.id) \
            .join(Platform, ShopInfo.platform_id == Platform.id)

        sql = select([text(['*'])]).select_from(lg_shop_sql).order_by('shop_info_sales').limit(rank_page_size)

        # ranks = pd.read_sql(sql_.SELECT_SHOP_INFO_PLATFORM_BRAND_LIMIT_ORDER_BY_SALES % rank_page_size, engine)
        ranks = pd.read_sql(sql, engine)
        # 商品结果
        ranks_data = self.get_dataframe_data(ranks, columns=['shop_info', 'platform', 'brand'])
        return Result.success(data=ranks_data)


class NowDayDataListView(NeedLoginView):
    """查询当天的数据"""

    def get(self, request):
        session = get_session(get_engine())
        now_zero = datetime.datetime.strptime(datetime.datetime.now().strftime("%Y-%m-%d"), "%Y-%m-%d")
        branch_count = session.query(Brand).filter(Brand.create_time >= now_zero).count()
        shop_count = session.query(ShopInfo).filter(ShopInfo.create_time >= now_zero).count()
        sum_money = session.query(func.sum(ShopInfo.money * ShopInfo.sales).label('sum')).filter(
            ShopInfo.create_time >= now_zero).first()

        return Result.success(data=[
            {
                'value': branch_count,
                'name': '品牌',
                'unit': '个'
            }, {
                'value': shop_count,
                'name': '商品',
                'unit': '个'
            }, {
                'value': sum_money['sum'] or 0,
                'name': '金额',
                'unit': '元'
            },
        ])


class AccountManageView(NeedLoginView, ExecuteSql):
    def get_dataframe_data(self, data):
        ret = []
        for item in data:
            ret.append({
                'id': item[0].id,
                'create_time': item[0].create_time,
                'update_time': item[0].update_time,
                'name': item[0].name,
                'password': item[0].password,
                'platform': {
                    'id': item[1].id,
                    'name': item[1].name,
                    'home_url': item[1].home_url,
                    'login_url': item[1].login_url,
                }
            })

        return ret

    def get(self, request):
        engine = get_engine()
        session = get_session(engine)
        data = session.query(Account, Platform).join(Platform, Platform.id == Account.platform_id).all()
        ret = self.get_dataframe_data(data)
        return Result.success(data=ret)


class PlatformView(NeedLoginView, CustomGenericViewSet):
    def get(self, request):
        df = pd.read_sql(str(select(['*']).select_from(Platform)), get_engine())
        return Result.success(data=df.to_dict('records'))

    def big_ping_top(self, request):
        """大屏展示金额"""
        engine = get_engine()
        sql = select([func.sum(ShopInfo.money * ShopInfo.sales).label('money'), ShopInfo.platform_id, Platform.name]) \
            .select_from(ShopInfo) \
            .join(Platform, ShopInfo.platform_id == Platform.id).group_by(ShopInfo.platform_id)

        df = pd.read_sql(str(sql), engine)

        # 获取所有的金额
        table_sql = select(
            [func.date_format(ShopInfo.update_time, '%Y-%m-%d').label('day'),
             func.sum(ShopInfo.money * ShopInfo.sales).label('money'), Platform.name.label('name')]) \
            .select_from(ShopInfo) \
            .join(Platform, ShopInfo.platform_id == Platform.id) \
            .group_by(column('day'), column('money'), column('name'))

        select_table_sql = select(
            [func.date_format(column('day'), '%Y-%m-%d').label('day'),
             func.sum(column('money')).label('m'), column('name').label('name')]) \
            .select_from(table_sql) \
            .group_by(column('name'), column('day'))

        ex_end_sql = select(text('*'), func.truncate(column('m'), 0).label('money')).select_from(
            select_table_sql.alias('a'))

        ex_sql = ex_end_sql.compile(engine, compile_kwargs={"literal_binds": True})
        d = pd.read_sql(str(ex_sql), engine)

        return Result.success(data=df.to_dict('records'), table=d.to_dict('records'), sql=str(ex_sql))


class BrandView(NeedLoginView):

    def post(self, request):
        data = request.data
        engine = get_engine()
        session = get_session(engine)
        tb_data = session.query(Brand, Platform).join(Platform, Brand.platform_id == Platform.id) \
            .filter(Platform.id.in_(data.get('platforms'))).all()
        ret = []
        for item in tb_data:
            n = item[0]
            p = item[1]
            r = {
                'id': n.id,
                'name': n.name,
                'platform_id': n.platform_id,
                'platform': {
                    'id': p.id,
                    'name': p.name,
                    'home_url': p.home_url,
                    'login_url': p.login_url,
                    'label': p.label,
                },
            }
            ret.append(r)

        return Result.success(data=ret)


class TableDataView(NeedLoginView):
    """表数据操作"""

    def post(self, request):
        data = request.data
        table_name = data['table_name']

        if table_name not in get_tables().keys():
            return Result.error(message=f"名为 {table_name} 的表不存在")

        listQuery = request.data['listQuery']
        page_size = int(listQuery.get('pageSize'))
        page = (int(listQuery.get('page')) - 1)

        ext_sql = select(['*']).select_from(table(table_name))
        count_sql = select([func.count('*').label('count')]).select_from(ext_sql).where()

        sql = ext_sql.order_by(text('-id')).offset(page * page_size).limit(
            page_size)
        engine = get_engine()
        df = pd.read_sql(sql, engine)
        count_df = pd.read_sql(count_sql, engine)
        ret_list_query = listQuery.copy()
        ret_list_query['total'] = count_df['count'][0]
        ret = field_alias_list(table_name)
        return Result.success(data={
            'title': "正在查看的表为：" + table_name,
            'tableData': df.to_dict('records'),
            'tableHeader': ret,
            'form_data': data,
            'listQuery': ret_list_query
        })

    def delete(self, request):
        data = request.data
        filter_data = data['filter_data']
        table_name = data['table_name']
        engine = get_engine()
        if 'id' in filter_data.keys():
            sql = delete(table(table_name)).where(column('id') == filter_data['id'])
        else:
            filters = [f'`{k}`="{v}"' for k, v in filter_data.items()]
            sql = """
            DELETE FROM {} WHERE {}
            """.format(table_name, ','.join(filters))

        ret = engine.execute(sql)

        return Result.success(data=ret.rowcount)


class LineDataView(NeedLoginView):
    """筛选数据可视化"""

    def post(self, request):
        data = request.data
        table_name = data['table_name']
        filter_data = data['filter_data']
        primary_keys = get_primary_keys(table_name)
        sql = select(['*']).where(primary_keys[0] == filter_data[primary_keys[0].name])

        df = pd.read_sql(sql, get_engine())

        return Result.success(data={
            'title': "表：" + table_name + ",列：" + str(filter_data[primary_keys[0].name]),
            'tableData': df.to_dict('records')[0],
            'form_data': data,
            'tableHeader': field_alias_list(table_name),
            'dontEdit': [i.name for i in primary_keys]
        })


class UpdateLineView(NeedLoginView):
    """更新单行数据"""

    def post(self, request):
        data = request.data
        table_name = data['table_name']
        filter_data = data['filter_data']
        copy_data = data['copyData']
        primary_keys = get_primary_keys(table_name)
        key_value = filter_data.pop(primary_keys[0].name)
        copy_data.pop(primary_keys[0].name)
        update_cause = {}
        for k, v in copy_data.items():
            if filter_data[k] != v:
                update_cause[k] = filter_data[k]

        engine = get_engine()
        and_cause = ' AND '.join([f'{k}="{v}"' for k, v in update_cause.items()])
        sql = sql_.UPDATE_LINE_WHERE_KEY.format(
            table_name,
            and_cause,
            primary_keys[0].name,
            key_value
        )
        engine.execute(sql)

        return Result.success()


class DownloadTable(NeedLoginView):
    """下载"""

    def post(self, request):
        table_name = request.data['table_name']
        sql = select(['*']).select_from(table(table_name))
        df = pd.read_sql(sql, get_engine())
        alias_list = field_alias_list(table_name)
        column_name = {i['column']: i['alias'] for i in alias_list}
        df.rename(columns=column_name, inplace=True)

        csv_response = others.csv_generate(df, table_name, sep=',')
        return csv_response


class LeaderBoardView(NeedLoginView):
    def post(self, request):
        data = request.data
        # 排行榜模式：config.Leader
        mode = data['mode']
        limit = data['limit']
        return Result.success(data={})
